﻿using System.Data;
using System;

namespace DMS.DB
{
    /// <summary>
    /// 
    /// </summary>
    public class StoredProcedures : StoredProcedures_Base
    {

        private MainDB _db;

        public StoredProcedures(MainDB db)
            : base(db)
        {
            _db = db;
        }

        #region Account

        public DataTable Login(string Username, string Password)
        {
            IDbCommand cmd = _db.CreateCommand("Login", true);
            _db.AddParameter(cmd, "Username", DbType.String, Username);
            _db.AddParameter(cmd, "Password", DbType.String, Password);
            return CreateDataTable(cmd);
        }

        public DataTable Insert_Accounts(string Account_Name,string Account_Password,string Account_Fullname,bool Account_Status,string Account_Email,
            string Account_Phone,string Account_Avatar,bool Account_SuperAdmin,int Brander_Id, int Account_Role, int Account_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Accounts", true);
            _db.AddParameter(cmd, "Account_Name", DbType.String, Account_Name);
            _db.AddParameter(cmd, "Account_Password", DbType.String, Account_Password);
            _db.AddParameter(cmd, "Account_Fullname", DbType.String, Account_Fullname);
            _db.AddParameter(cmd, "Account_Status", DbType.Boolean, Account_Status);
            _db.AddParameter(cmd, "Account_Email", DbType.String, Account_Email);
            _db.AddParameter(cmd, "Account_Phone", DbType.String, Account_Phone);
            _db.AddParameter(cmd, "Account_Avatar", DbType.String, Account_Avatar);
            _db.AddParameter(cmd, "Account_SuperAdmin", DbType.Boolean, Account_SuperAdmin);
            _db.AddParameter(cmd, "Brander_Id", DbType.Int32, Brander_Id);
            _db.AddParameter(cmd, "Account_Role", DbType.Int32, Account_Role);
            _db.AddParameter(cmd, "Account_Id", DbType.Int32, Account_Id);
            return CreateDataTable(cmd);
        }

        public void Update_Accounts(int Account_Id, string Account_Fullname, bool Account_Status, string Account_Email, string Account_Phone, string Account_Avatar, bool Account_SuperAdmin, int Brander_Id, int Account_Role)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Accounts", true);
            _db.AddParameter(cmd, "Account_Id", DbType.Int32, Account_Id);
            _db.AddParameter(cmd, "Account_Fullname", DbType.String, Account_Fullname);
            _db.AddParameter(cmd, "Account_Status", DbType.Boolean, Account_Status);
            _db.AddParameter(cmd, "Account_Email", DbType.String, Account_Email);
            _db.AddParameter(cmd, "Account_Phone", DbType.String, Account_Phone);
            _db.AddParameter(cmd, "Account_Avatar", DbType.String, Account_Avatar);
            _db.AddParameter(cmd, "Account_SuperAdmin", DbType.Boolean, Account_SuperAdmin);
            _db.AddParameter(cmd, "Brander_Id", DbType.Int32, Brander_Id);
            _db.AddParameter(cmd, "Account_Role", DbType.Int32, Account_Role);
            CreateDataTable(cmd);
        }

        public DataTable Select_Account_by_Id(int Account_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Account_by_Id", true);
            _db.AddParameter(cmd, "Account_Id", DbType.Int32, Account_Id);
            return CreateDataTable(cmd);
        }

        public DataTable Select_Account_by_Name(string Account_Name)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Account_by_Name", true);
            _db.AddParameter(cmd, "Account_Name", DbType.String, Account_Name);
            return CreateDataTable(cmd);
        }

        public void Update_Password(int Account_Id, string Password)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Password", true);
            _db.AddParameter(cmd, "Account_Id", DbType.Int32, Account_Id);
            _db.AddParameter(cmd, "Password", DbType.String, Password);
            CreateDataTable(cmd);
        }

        /// <summary>
        /// Danh sach tat ca cac account
        /// </summary>
        /// <param name="SuperAdmin">0: lay tat ca cac user</param>
        /// <returns></returns>
        public DataTable Select_Accounts(int SuperAdmin)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Accounts", true);
            _db.AddParameter(cmd, "SuperAdmin", DbType.Int32, SuperAdmin);
            return CreateDataTable(cmd);
        }

        public void Change_Password(int Account_Id, string Password)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Account_by_Id", true);
            _db.AddParameter(cmd, "Account_Id", DbType.Int32, Account_Id);
            _db.AddParameter(cmd, "Password", DbType.String, Password);
            CreateDataTable(cmd);
        }

        public void Delete_Accounts(int Account_ID)
        {
            IDbCommand cmd = _db.CreateCommand("Delete_Accounts", true);

            _db.AddParameter(cmd, "Account_ID", DbType.Int32, Account_ID);

            CreateDataTable(cmd);
        }
        #endregion

        #region Caregories

        /// <summary>
        /// Thong tin chuyen muc
        /// </summary>
        /// <param name="CategoryId"></param>
        /// <param name="CategoryActive">-1: Lay tat ca ko quan tam CategoryActive; 0-1: lay theo Active</param>
        /// <returns></returns>
        public DataTable Category_Select_by_Id(int CategoryId, int CategoryActive)
        {
            IDbCommand cmd = _db.CreateCommand("Category_Select_by_Id", true);

            _db.AddParameter(cmd, "CategoryId", DbType.Int32, CategoryId);
            _db.AddParameter(cmd, "CategoryActive", DbType.Int32, CategoryActive);

            return CreateDataTable(cmd);
        }
        /// <summary>
        /// Lay danh sach category theo cat parent
        /// </summary>
        /// <param name="CategoryParentId"></param>
        /// <param name="CategoryActive">-1: lay tat ca; 0-1: lay theo trang thai</param>
        /// <returns></returns>
        public DataTable Category_Select_by_ParentId(int CategoryParentId, int CategoryActive)
        {
            IDbCommand cmd = _db.CreateCommand("Category_Select_by_ParentId", true);

            _db.AddParameter(cmd, "CategoryParentId", DbType.Int32, CategoryParentId);
            _db.AddParameter(cmd, "CategoryActive", DbType.Int32, CategoryActive);

            return CreateDataTable(cmd);
        }

        /// <summary>
        /// Lay tat ca cac chuyen muc
        /// </summary>
        /// <param name="CategoryActive">-1: lay tat ca; 0-1: lay theo trang thai</param>
        /// <returns></returns>
        public DataTable Category_Select_All(int CategoryActive)
        {
            IDbCommand cmd = _db.CreateCommand("Category_Select_All", true);
            _db.AddParameter(cmd, "CategoryActive", DbType.Int32, CategoryActive);

            return CreateDataTable(cmd);
        }

        public DataTable Category_Insert(string CategoryName, string CategoryDescription, int CategoryParentId, bool CategoryActive)
        {
            IDbCommand cmd = _db.CreateCommand("Category_Insert", true);

            _db.AddParameter(cmd, "CategoryName", DbType.String, CategoryName);
            _db.AddParameter(cmd, "CategoryDescription", DbType.String, CategoryDescription);
            _db.AddParameter(cmd, "CategoryParentId", DbType.Int32, CategoryParentId);
            _db.AddParameter(cmd, "CategoryActive", DbType.Boolean, CategoryActive);

            return CreateDataTable(cmd);
        }

        public void Category_Update(int CategoryId, string CategoryName, string CategoryDescription, int CategoryParentId, bool CategoryActive)
        {
            IDbCommand cmd = _db.CreateCommand("Category_Update", true);
            _db.AddParameter(cmd, "CategoryId", DbType.Int32, CategoryId);
            _db.AddParameter(cmd, "CategoryName", DbType.String, CategoryName);
            _db.AddParameter(cmd, "CategoryDescription", DbType.String, CategoryDescription);
            _db.AddParameter(cmd, "CategoryParentId", DbType.Int32, CategoryParentId);
            _db.AddParameter(cmd, "CategoryActive", DbType.Boolean, CategoryActive);

            CreateDataTable(cmd);
        }

        #endregion

        #region Documents
        public DataTable Document_Insert(int CategoryId, string DocumentName, string DocumentCode,
                                    string DocumentDescription, string DocumentContent,
                                    string DocumentLink, string DocumentImage, int DocumentType,
                                    bool Active, bool Appoved, string CategoryOther)
        {
            IDbCommand cmd = _db.CreateCommand("Document_Insert", true);

            _db.AddParameter(cmd, "CategoryId", DbType.Int32, CategoryId);
            _db.AddParameter(cmd, "DocumentName", DbType.String, DocumentName);
            _db.AddParameter(cmd, "DocumentCode", DbType.String, DocumentCode);
            _db.AddParameter(cmd, "DocumentDescription", DbType.String, DocumentDescription);
            _db.AddParameter(cmd, "DocumentContent", DbType.String, DocumentContent);
            _db.AddParameter(cmd, "DocumentLink", DbType.String, DocumentLink);
            _db.AddParameter(cmd, "DocumentImage", DbType.String, DocumentImage);
            _db.AddParameter(cmd, "DocumentType", DbType.Int32, DocumentType);
            _db.AddParameter(cmd, "Active", DbType.Boolean, Active);
            _db.AddParameter(cmd, "Appoved", DbType.Boolean, Appoved);
            _db.AddParameter(cmd, "CategoryOther", DbType.String, CategoryOther);

            return CreateDataTable(cmd);
        }

        public void Document_Update(int DocumentId, int CategoryId, string DocumentName, string DocumentCode,
                                    string DocumentDescription, string DocumentContent,
                                    string DocumentLink, string DocumentImage, int DocumentType,
                                    bool Active, bool Appoved, string CategoryOther)
        {
            IDbCommand cmd = _db.CreateCommand("Document_Update", true);

            _db.AddParameter(cmd, "DocumentId", DbType.Int32, DocumentId);
            _db.AddParameter(cmd, "CategoryId", DbType.Int32, CategoryId);
            _db.AddParameter(cmd, "DocumentName", DbType.String, DocumentName);
            _db.AddParameter(cmd, "DocumentCode", DbType.String, DocumentCode);
            _db.AddParameter(cmd, "DocumentDescription", DbType.String, DocumentDescription);
            _db.AddParameter(cmd, "DocumentContent", DbType.String, DocumentContent);
            _db.AddParameter(cmd, "DocumentLink", DbType.String, DocumentLink);
            _db.AddParameter(cmd, "DocumentImage", DbType.String, DocumentImage);
            _db.AddParameter(cmd, "DocumentType", DbType.Int32, DocumentType);
            _db.AddParameter(cmd, "Active", DbType.Boolean, Active);
            _db.AddParameter(cmd, "Appoved", DbType.Boolean, Appoved);
            _db.AddParameter(cmd, "CategoryOther", DbType.String, CategoryOther);

            CreateDataTable(cmd);
        }

        public DataTable Document_Select_All()
        {
            IDbCommand cmd = _db.CreateCommand("Document_Select_All", true);
            return CreateDataTable(cmd);
        }

        public DataTable Document_Select_by_Id(int DocumentId)
        {
            IDbCommand cmd = _db.CreateCommand("Document_Select_by_Id", true);
            _db.AddParameter(cmd, "DocumentId", DbType.Int32, DocumentId);
            return CreateDataTable(cmd);
        }


        /// <summary>
        /// Danh sach tai lieu theo chuyen muc
        /// </summary>
        /// <param name="CategoryId"></param>
        /// <returns></returns>
        public DataTable Document_Select_by_CatId(int CategoryId)
        {
            IDbCommand cmd = _db.CreateCommand("Document_Select_by_CatId", true);
            _db.AddParameter(cmd, "CategoryId", DbType.Int32, CategoryId);
            return CreateDataTable(cmd);
        }

        public void Document_Delete(int DocumentId)
        {
            IDbCommand cmd = _db.CreateCommand("Document_Delete", true);
            _db.AddParameter(cmd, "DocumentId", DbType.Int32, DocumentId);
            CreateDataTable(cmd);
        }

        #endregion

        #region Banners
        public DataTable Banner_Insert(string BannerName, string BannerDescription, string BannerContent, int BannerPosition, int BannerType, bool BannerActive, 
                                        DateTime StartDate, DateTime EndDate)
        {
            IDbCommand cmd = _db.CreateCommand("Banner_Insert", true);

            _db.AddParameter(cmd, "BannerName", DbType.String, BannerName);
            _db.AddParameter(cmd, "BannerDescription", DbType.String, BannerDescription);
            _db.AddParameter(cmd, "BannerContent", DbType.String, BannerContent);
            _db.AddParameter(cmd, "BannerPosition", DbType.String, BannerPosition);
            _db.AddParameter(cmd, "BannerType", DbType.Int32, BannerType);
            _db.AddParameter(cmd, "BannerActive", DbType.Boolean, BannerActive);
            _db.AddParameter(cmd, "StartDate", DbType.DateTime, StartDate);
            _db.AddParameter(cmd, "EndDate", DbType.DateTime, EndDate);

            return CreateDataTable(cmd);
        }

        public void Banner_Update(int BannerId, string BannerName, string BannerDescription, string BannerContent, int BannerPosition, int BannerType, bool BannerActive,
                                        DateTime StartDate, DateTime EndDate)
        {
            IDbCommand cmd = _db.CreateCommand("Banner_Update", true);

            _db.AddParameter(cmd, "BannerId", DbType.Int32, BannerId);
            _db.AddParameter(cmd, "BannerName", DbType.String, BannerName);
            _db.AddParameter(cmd, "BannerDescription", DbType.String, BannerDescription);
            _db.AddParameter(cmd, "BannerContent", DbType.String, BannerContent);
            _db.AddParameter(cmd, "BannerPosition", DbType.String, BannerPosition);
            _db.AddParameter(cmd, "BannerType", DbType.Int32, BannerType);
            _db.AddParameter(cmd, "BannerActive", DbType.Boolean, BannerActive);
            _db.AddParameter(cmd, "StartDate", DbType.DateTime, StartDate);
            _db.AddParameter(cmd, "EndDate", DbType.DateTime, EndDate);

            CreateDataTable(cmd);
        }

        public DataTable Banner_Select()
        {
            IDbCommand cmd = _db.CreateCommand("Banner_Select", true);
            return CreateDataTable(cmd);
        }

        public DataTable Banner_Select_by_Id(int BannerId)
        {
            IDbCommand cmd = _db.CreateCommand("Banner_Select_by_Id", true);
            _db.AddParameter(cmd, "BannerId", DbType.Int32, BannerId);
            return CreateDataTable(cmd);
        }

        public DataTable Banner_Select_FE(int BannerPosition)
        {
            IDbCommand cmd = _db.CreateCommand("Banner_Select_FE", true);
            _db.AddParameter(cmd, "BannerPosition", DbType.Int32, BannerPosition);
            return CreateDataTable(cmd);
        }

        #endregion        
    }
} // End of namespace



